from app01 import models
from django.db.models import Count
from datetime import datetime, timedelta
import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.styles import Alignment
from django.core.mail import send_mail


class DateTool:
    @staticmethod
    def get_date_property(date, type):
        date_obj = datetime.strptime(date, "%Y年%m月%d日 %H:%M")

        if type == "year":
            return date_obj.year
        elif type == "month":
            return date_obj.month
        elif type == "day":
            return date_obj.day
        elif type == "hour":
            return date_obj.hour
        elif type == "minute":
            return date_obj.minute
        else:
            return None


class ChartTime:
    def __init__(self, time):
        today = datetime.today()  # 获取今天的日期
        dates = [str(today.strftime('%d')) + "日"]  # 将今天的日期添加到列表中
        for i in range(1, time):
            date = str((today - timedelta(days=i)).strftime('%d')) + "日"  # 获取前time天的日期
            dates.append(date)  # 将日期添加到列表中

        dates.reverse()  # 反转列表，使日期按照从前到后的顺序排列
        self.dates = dates
        # 计算最近七天的起始日期和结束日期
        today_date = datetime.today()
        end_date = today_date.replace(hour=0, minute=0, second=0, microsecond=0, )
        start_date = end_date - timedelta(days=time - 1)

        # 查询每天的数据记录数量
        date_counts = []
        current_date = start_date
        while current_date <= end_date:
            next_date = current_date + timedelta(days=1)
            record_count = models.Record.objects.filter(signin_time__range=[current_date, next_date]).count()
            date_counts.append(record_count)
            current_date = next_date

        self.date_counts = date_counts

    def get_counts(self):
        return self.date_counts

    def get_dates(self):
        return self.dates


class ChartHourTime:
    def __init__(self, time):

        # 获取当前时间
        now = datetime.now()

        # 计算最近time天的时间范围
        start_date = now - timedelta(days=time)
        end_date = now

        # 构建查询
        query = models.Record.objects.filter(signin_time__range=(start_date, end_date)).values(
            'signin_time__hour').annotate(
            count=Count('id'))

        # 初始化结果字典
        result = {}

        # 将查询结果添加到结果字典中
        for item in query:
            hour = item['signin_time__hour']
            if hour == 0:
                hour = 24
            time_period = f'{hour}点'
            result[time_period] = item['count']

        # 输出结果
        # for time_period, count in result.items():
        #     print(f'{time_period}: {count}')

        self.date_hour = []
        for time_period, count in result.items():
            self.date_hour.append(time_period)

        self.date_count = []
        for time_period, count in result.items():
            self.date_count.append(count)

    def get_hours(self):
        return self.date_hour

    def get_counts(self):
        return self.date_count


class CharDepartmentTime:
    def __init__(self, time):
        # 获取当前时间
        now = datetime.now()

        # 计算最近time天的时间范围
        start_date = now - timedelta(days=time)
        end_date = now

        # 构建查询
        query = models.Record.objects.filter(signin_time__range=(start_date, end_date)).values(
            'user_info__depart__title').annotate(
            count=Count('id'))

        # 初始化结果列表
        self.depart_counts = []

        # 将查询结果添加到结果字典中
        for item in query:
            result = {}
            name = item['user_info__depart__title']
            result["value"] = item['count']
            result["name"] = name
            self.depart_counts.append(result)

    def get_departs(self):
        return self.depart_counts


class IsSignIn:
    @staticmethod
    def get_result(nid):
        # 计算最近1天的时间范围
        today_date = datetime.today() + timedelta(days=1)
        end_date = today_date.replace(hour=0, minute=0, second=0, microsecond=0, )
        start_date = end_date - timedelta(days=1)

        # 构建查询
        exists = models.Record.objects.filter(signin_time__range=(start_date, end_date), user_info_id=nid).exists()

        return exists


class TimePeriod:
    @staticmethod
    def get_result(time):
        # 计算最近time天的时间范围
        today_date = datetime.today() + timedelta(days=1)
        end_date = today_date.replace(hour=0, minute=0, second=0, microsecond=0, )
        start_date = end_date - timedelta(days=time)

        # 构建查询
        queryset = models.Record.objects.filter(signin_time__range=(start_date, end_date)).order_by('-id')

        return queryset


class SendData:
    @staticmethod
    def send_email(time, email):
        set_time = time
        set_time = int(set_time)
        # 查询数据库获取数据，假设获取到的数据存储在records变量中
        records = TimePeriod().get_result(set_time)
        # 创建一个新的Excel文件
        workbook = openpyxl.Workbook()
        worksheet = workbook.active
        # 写入第一行表头
        headers = ['ID', '签到时间', '学号', '姓名', '性别', '所属部门', 'IP地址', '是否人脸']
        for col_num, header in enumerate(headers, 1):
            col_letter = get_column_letter(col_num)
            cell = worksheet.cell(row=1, column=col_num, value=header)
            cell.alignment = Alignment(horizontal='center')
        # 写入数据
        for row_num, record in enumerate(records, 2):
            worksheet.cell(row=row_num, column=1, value=record.id)
            worksheet.cell(row=row_num, column=2, value=record.signin_time.strftime("%Y年%m月%d日 %H:%M"))
            worksheet.cell(row=row_num, column=3, value=record.user_info.sid)
            worksheet.cell(row=row_num, column=4, value=record.user_info.name)
            worksheet.cell(row=row_num, column=5, value=record.user_info.get_gender_display())
            worksheet.cell(row=row_num, column=6, value=record.user_info.depart.title)
            worksheet.cell(row=row_num, column=7, value=record.ip)
            worksheet.cell(row=row_num, column=8, value=record.get_is_face_display())
        # 设置列宽
        for col_num in range(1, len(headers) + 1):
            col_letter = get_column_letter(col_num)
            worksheet.column_dimensions[col_letter].width = 15

        # 生成 Excel 文件保存的路径和文件名
        file_path = '/media/excel/data.xlsx'

        # 保存 Excel 文件
        workbook.save(file_path)

        # 构造邮件内容
        subject = f'最近{set_time}天的签到记录'
        message = '请查找附件中的excel文件。'
        from_email = 'your_email@example.com'
        recipient_list = email

        # 发送邮件
        send_mail(subject, message, from_email, recipient_list, fail_silently=False,
                  attachment=[file_path])

        # 删除生成的 Excel 文件
        os.remove(file_path)
